package com.genius.unite_platform.goods_class.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.util.ConnectionException;
import com.genius.workflow.util.DBClosedException;
import com.genius.workflow.util.DBUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.*;

public class DBTools1 {
	
	public static enum DBType{
		MySql,
		Oracle,
		SqlServer
	}
	/**
	 * 普通的sql查询语句
	 * @param field
	 * @param table
	 * @return
	 */
	public static String getSelectSql(String field,String table){
    	
		String newTable = "";
		int selectIndexOf = table.toLowerCase().indexOf("select");
		if(selectIndexOf>0){
			newTable = "("+table+")ta1";
		}else{
			newTable = table;
		}
    	String sql = "select "+field+" from "+newTable;
    	return sql;
	}
	/**
	 * 查询语句把参数自动生成
	 * @param field 根据指定的字段 生成指定的参数值 value是？的值
	 * @param json  参数值合集
	 * @return
	 */
	public static String[] getSelectParms(String whereField,JSONObject json){
		return getParms(whereField, json);
	}
	public static String getInsertSql(String field,String table){
		String [] fields = field.split(DefaultConfiguration.separator_douhao);
		String insertField = "";
		for(String f:fields){
			if(f.contains("=")){
				String spil [] = f.split("=");
				insertField += spil[0]+",";
			}else{
				insertField += f+",";
			}
		}
		insertField = insertField.substring(0, insertField.lastIndexOf(","));
		String sql = "insert into "+table+"("+insertField+")"+getInsertSqlValues(field);
		return sql;
	}
	public static String getReplaceInsertSql(String field,String table){
		String sql = "replace into "+table+"("+field+")"+getInsertSqlValues(field);
		return sql;
	}
	private static String getInsertSqlValues(String field){
		String values = " values(";
		String [] fields = field.split(DefaultConfiguration.separator_douhao);
		for(int i=0;i<fields.length;i++){
			String f = fields[i];
			if(f.contains("=")){
				values += f.split("=")[1]+",";
			}else{
				values += "?,";
			}
		}
		values = values.substring(0, values.lastIndexOf(","))+")";
		return values;
	}
	/**
	 * 生成所有inser语句需要的参数
	 * @param field
	 * @param json
	 * @return
	 */
	public static String[] getInsertParms(String field,JSONObject json){
		return getParms(field, json);
	}
	public static List<String[]> getInsertListParms(String field,JSONArray arr){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			result.add(getInsertParms(field, arr.getJSONObject(index)));
		}
		return result;
	}
	public static String[] getUpdateParms(String field,JSONObject json){
		return getParms(field, json);
	}
	/**
	 * 生成所有update语句需要的参数
	 * @param field
	 * @param json
	 * @param whereField  如果有where条件，把条件的字段也最加上，前提是json中包含有whereField的字段
	 * @return
	 */
	public static String[] getUpdateAndWhereParms(String field,JSONObject json,Object ...whereField){
		return getParms(field, json,whereField);
	}
	/**
	 * 更新多条数据时获取条件的参数合集
	 * @param field
	 * @param arr
	 * @param whereField
	 * @return
	 */
	public static List<String[]> getUpdateListParms(String field,JSONArray arr,Object ...whereField){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			result.add(getUpdateAndWhereParms(field, arr.getJSONObject(index), whereField));
		}
		return result;
	}
	
	public static String getDelSql(String table){
		String sql = "delete from "+table;
		return sql;
	}
	public static String[] getDeleteParms(String field,JSONObject json){
		return getParms(field, json);
	}
	public static List<String[]> getDeleteListParms(String field,JSONArray arr){
		ArrayList<String[]> result = new ArrayList<>();
		for(int index=0;index<arr.size();index++){
			result.add(getDeleteParms(field, arr.getJSONObject(index)));
		}
		return result;
	}
	private static String[] JoinNewArr(String str,String [] arr){
		String [] newArr = new String[arr.length+1];
		for(int i=0;i<arr.length;i++){
			newArr[i] = arr[i];
		}
		if(arr.length==0)
			newArr[0] = str;
		else
			newArr[arr.length] = str;
		return newArr;
	}
	/**
	 * 
	 * @param field
	 * @param json  拼接的数据参数
	 * @param whereField 如果不为空说明是有where语句的参数，追加之的操作
	 * @return
	 */
	private static String[] getParms(String field,JSONObject json,Object ...whereField){
		String [] trans_fields = field.split(DefaultConfiguration.separator_douhao);
		String [] fields = new String[0];
		for(String editField:trans_fields){
			if(!editField.contains("="))
				fields = JoinNewArr(editField, fields);
		}
		int length = fields.length;
		String [] whereFieldArr = null;
		if(whereField.length !=0){
			String whereFieldStr = whereField[0].toString();
			whereFieldArr = whereFieldStr.split(DefaultConfiguration.separator_douhao);
			length = length+whereFieldArr.length;
		}
		String [] result = new String[length];
		for(int index=0;index<fields.length;index++){
			result[index] = json.containsKey(fields[index])?json.getString(fields[index]):"";
		}
		if(whereField.length !=0){
			for(int index=0;index<whereFieldArr.length;index++){
				String key = (String)whereFieldArr[index];
				result[fields.length+index] = json.containsKey(key)?json.getString(key):"";
			}
		}
		return result;
	}
	/**
	 * 
	 * @param field
	 * @param json  拼接的数据参数
	 * @param isFirst 字段的第一个是否为 ？
	 * @return
	 */
	private static String getUpdateSetSql(String field){
		
		String [] fields = field.split(DefaultConfiguration.separator_douhao);
		String set = " set ";
		for(String f:fields){
			if(f.contains("=")){
				set += f+",";
			}
		}
		set = set.substring(0, set.lastIndexOf(","));
		return set;
	}
	/**
	 * 获取update的语句
	 * @param field
	 * @param table
	 * @return
	 */
	public static String getUpdateSql(String field,String table){
		String sql = "update "+table+getUpdateSetSql(field);
		return sql;
	}
	/**
	 * 获取update的语句
	 * @param field
	 * @param table
	 * @param whereField  where条件也加上
	 * @return
	 */
	public static String getUpdateAndWhereSql(String field,String table,String whereField){
		String sql = "update "+table+getUpdateSetSql(field)+"where "+whereField;
		return sql;
	}
	/**
	 * 插入数据操作
	 * @param insertField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static int[] insert(String insertField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException {
		String insertSql = DBTools1.getInsertSql(insertField, table);
		String [] parms = DBTools1.getInsertParms(insertField, json);
		return insert(insertSql,parms);
	}
	public static int [] insert(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException {
		SQLBean bean = new SQLBean();
 		bean.setSql(sql);
 		bean.setParms(parms);
		return executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 批量插入数据操作
	 * @param insertField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void insertBatch(String insertField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException {
		String insertSql = DBTools1.getInsertSql(insertField, table);
		List<String[]> parms = DBTools1.getInsertListParms(insertField, arr);
		insertBatch(insertSql, parms);
	}
	public static void insertBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException {
		SQLBean bean = new SQLBean();
 		bean.setSql(sql);
 		bean.setParms(parms);
 		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	public static int [] executeBatch(LinkedList<SQLBean>beanList) throws ConnectionException, DBClosedException, SQLException{
 		return DBUtils.executeBatch(beanList);
	}
	public static int [] execute(SQLBean bean) throws ConnectionException, DBClosedException, SQLException{
 		return DBUtils.execute(bean);
	}
	/**
	 * 更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void update(String updateField,String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String updateSql = DBTools1.getUpdateAndWhereSql(updateField, table, whereField);
		String [] parms = DBTools1.getUpdateAndWhereParms(updateField, json,whereField);
 		update(updateSql, parms);
	}
	public static void update(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
 		bean.setSql(sql);
 		bean.setParms(parms);
 		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 批量更新操作
	 * @param updateField
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void updateBatch(String updateField,String whereField,String table,JSONArray arr) throws ConnectionException, DBClosedException, SQLException{
		String updateSql = DBTools1.getUpdateAndWhereSql(updateField, table, whereField);
		List<String[]> parms = DBTools1.getUpdateListParms(updateField, arr, whereField);
		updateBatch(updateSql, parms);
	}
	public static void updateBatch(String sql,List<String[]> parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
 		bean.setSql(sql);
 		bean.setParms(parms);
 		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 删除数据操作
	 * @param whereField
	 * @param table
	 * @param json
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static void delete(String sql,String [] parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
		bean.setSql(sql);
		bean.setParms(parms);
		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	public static void deleteBach(String sql,List<String[]>parms) throws ConnectionException, DBClosedException, SQLException{
		SQLBean bean = new SQLBean();
 		bean.setSql(sql);
 		bean.setParms(parms);
 		executeBatch(new LinkedList(Arrays.asList(bean)));
	}
	/**
	 * 查询操作
	 * @param selectField
	 * @param table
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject select(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{
		return select(selectField, table, null,null);
	}
	public static JSONObject select(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
 		return DBUtils.select(sql, params);
	}
	/**
	 * 查询操作
	 * @param selectField 查询的字段
	 * @param table 查询的表名
	 * @param whereField where条件字段
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONArray selectArr(String selectField,String table) throws ConnectionException, DBClosedException, SQLException{
		
		return selectArr(selectField, table, null, null);
	}
	public static JSONArray selectArr(String sql,Object ... params) throws ConnectionException, DBClosedException, SQLException{
		return DBUtils.selectArr(sql,params);
	}
	public static String getSelectSqlByDBType(DBType type,String headField,String select,int page,int limit,String ascField,String asc,Object searchWhere){
		String sql = "";
		switch(type){
			case MySql:
				sql = selectByMySql(headField,select, page, limit, ascField, asc, searchWhere);
				break;
		}
		return sql;
	}
	private static String selectByMySql(String headField,String select,int page,int limit,String ascField,String asc,Object searchWhere){
		String where = "";
		if( searchWhere instanceof JSONObject){
			JSONObject search = (JSONObject)searchWhere;
			if(search != null){
				for(String key:search.keySet()){
					String value = search.getString(key);
					if(StringUtils.isNotBlank(value))
						where += key+" like "+"'%"+value+"%' and ";
				}
			}
			
		}
		if( searchWhere instanceof JSONArray){
			JSONArray search = (JSONArray)searchWhere;
			for(int index=0;index<search.size();index++){
				JSONObject json = search.getJSONObject(index);
				String conditionField = json.getString("conditionField");
				String conditionValue = json.getString("conditionValue");
				String conditionOption = json.getString("conditionOption");
				String logicalOperator = json.getString("logicalOperator");
				String conditionValueLeft = json.getString("conditionValueLeft");
				String conditionValueRight = json.getString("conditionValueRight");
				if(conditionOption.equals("between")){
					conditionValue = "'"+conditionValueLeft+"' and '"+conditionValueRight+"'";
				}
				where += where + conditionField+" "+conditionOption+" "+conditionValue+" "+logicalOperator+" ";
			}
		}
		String whereConnect = " where ";
		String lowerCaseSelect = select.toLowerCase();
		int length = lowerCaseSelect.lastIndexOf(")");
		//先看整体sql最后的括号后面是否有where关键字
		if( length > 0 ){
			String lastSelect = lowerCaseSelect.substring(length+1);
			if(lastSelect.indexOf("where")>0)
				whereConnect = " and ";
		}else{
			//再看整体sql如果没有括号，说明是简单是sql，再看sql中是否有where关键字
			if(lowerCaseSelect.indexOf("where")>0)
				whereConnect = " and ";
		}
		if(StringUtils.isNotBlank(where))
			where = whereConnect+where.substring(0, where.lastIndexOf("and "));
		String orderBy = "";
		if(asc !=null){
			orderBy = " order by "+ascField+" "+asc;
		}
		String limitPage = "";
		if(page != 0 && limit !=0){
			limitPage = " limit "+(page-1)*limit+","+limit;
		}
		String selectHead = "select SQL_CALC_FOUND_ROWS"+
				select.substring(lowerCaseSelect.indexOf("select")+6)+
				where+orderBy+limitPage;
		if(headField != null){
			String sql = select+where+orderBy;
			selectHead = "select SQL_CALC_FOUND_ROWS "+headField+" from ("+sql+")a"+limitPage;
		}
		
		return selectHead;
	}
	/**
	 * 默认根据字段进行排序
	 * @param type
	 * @param select
	 * @param table
	 * @param page
	 * @param limit
	 * @param asc
	 * @param ascField
	 * @return
	 */
	public static String getSelectPageSql(DBType type,String headField,String select,int page,int limit,String ascField,String asc){
		return getSelectSqlByDBType(type, headField,select, page, limit, ascField, asc,null);
	}
	/**
	 * 排序+条件查询
	 * @param type
	 * @param select
	 * @param page
	 * @param limit
	 * @param asc
	 * @param ascField
	 * @param searchWhere
	 * @return
	 */
	public static String getSelectPageSql(DBType type,String headField,String select,int page,int limit,String ascField,String asc,JSONObject searchWhere){
		return getSelectSqlByDBType(type, headField, select, page, limit, ascField, asc, searchWhere);
	}
	/**
	 * 普通where查询
	 * @param type
	 * @param select
	 * @param page
	 * @param limit
	 * @param search
	 * @return
	 */
	public static String getSelectPageSql(DBType type,String headField,String select,int page,int limit,JSONObject search){
		return getSelectSqlByDBType(type, headField, select,  page, limit, null, null, search);
	}
	/**
	 * 高级查询
	 * @param type
	 * @param select
	 * @param page
	 * @param limit
	 * @param searchWhere
	 * @return
	 */
	public static String getSelectPageSqlByHigh(DBType type,String headField,String select,int page,int limit,String ascField,String asc,JSONArray searchWhere){
		return getSelectSqlByDBType(type, headField, select, page, limit, ascField, asc, searchWhere);
	}
	public static String getSelectPageSqlByHigh(DBType type,String headField,String select,int page,int limit,JSONArray searchWhere){
		return getSelectSqlByDBType(type, headField, select, page, limit, null, null, searchWhere);
	}
	/**
	 * 判断是否有数据
	 * @param keyWords
	 * @param table
	 * @param json
	 * @return
	 * @throws ConnectionException
	 * @throws DBClosedException
	 * @throws SQLException
	 */
	public static JSONObject getIsExist(String whereField,String table,JSONObject json) throws ConnectionException, DBClosedException, SQLException{
		String where = " where ";
		for(String keyWord:whereField.split(",")){
			where += keyWord+"=? and ";
		}
		where = where.substring(0, where.lastIndexOf("and"));
		String sql = "select 1 from "+table+where;
		String [] parms = DBTools1.getSelectParms(whereField, json);
		return DBUtils.select(sql, parms);
	}
	public static JSONArray export(DBType type,String headField,String select,JSONObject search) throws ConnectionException, DBClosedException, SQLException{
		String sql = getSelectPageSql(type, headField, select, 0, 0, search);
		JSONArray arr = DBUtils.selectArr(sql);
		return arr;
	}
	public static JSONObject getFilling(HashMap<String, String>sqlMap) throws ConnectionException, DBClosedException, SQLException{
		JSONObject	json = new JSONObject();
		for(String key:sqlMap.keySet()){
			JSONArray keyJson = DBUtils.selectArr(sqlMap.get(key));
			json.put(key, keyJson);
		}
		return json;
	}
}
